﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace Sustainalytics.ClientDW.Synchronization.SyncProductInvolvement
{
    public class PISqlDatabase
    {
        private readonly string _connectionString;
        private readonly int _commandTimeout;

        public PISqlDatabase(string connectionString, int commandTimeout)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentException("Connection string must be provided!");

            _connectionString = connectionString;
            _commandTimeout = commandTimeout;
        }

        public List<PISqlRow> GetData(string storedProcedure)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                return (conn.Query<PISqlRow>(storedProcedure, null, null, true, _commandTimeout, System.Data.CommandType.StoredProcedure)).ToList();
            }
        }

        public List<CompanyIdsRow> GetCompanyIds(string storedProcedure)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                return (conn.Query<CompanyIdsRow>(storedProcedure, null, null, true, _commandTimeout, System.Data.CommandType.StoredProcedure)).ToList();
            };
        }

        public List<PIIndicatorsRow> GetIndicatorData(string storedProcedure)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                return (conn.Query<PIIndicatorsRow>(storedProcedure, null, null, true, _commandTimeout, System.Data.CommandType.StoredProcedure)).ToList();
            }
        }

        public List<SolutionIndicatorRow> GetSolutionData(string storedProcedure)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                return (conn.Query<SolutionIndicatorRow>(storedProcedure, null, null, true, _commandTimeout, System.Data.CommandType.StoredProcedure)).ToList();
            }
        }

        public class PISqlRow : CompanyIdsRow
        {
            public long SustCompanyId { get; set; }
            public string IndicatorSymbol { get; set; }
            public string CategorySymbol { get; set; }
            public string FieldKey { get; set; }
            public string FieldValue { get; set; }
        }

        public class CompanyIdsRow
        {
            public Guid CompanyId { get; set; }
        }

        public class SolutionIndicatorRow
        {
            public string Symbol { get; set; }
            public string Involvement { get; set; }
            public string RelatedCategory { get; set; }
            public string Description { get; set; }
            public string Revenue_range_1 { get; set; }
            public int? RR1_score { get; set; }
            public string Revenue_range_2 { get; set; }
            public int? RR2_score { get; set; }
            public string Revenue_range_3 { get; set; }
            public int? RR3_score { get; set; }
            public string Revenue_range_4 { get; set; }
            public int? RR4_score { get; set; }
            public string Revenue_range_5 { get; set; }
            public int? RR5_score { get; set; }
            public string Name { get; set; }
            public string IndicatorSymbol { get; set; }
        }

        public class PIIndicatorsRow : SolutionIndicatorRow
        {
            public string Ownership_Yes_No { get; set; }
            public int? Ownership_Score { get; set; }
            public string Revenue_range_Yes_No { get; set; }
            public int? Score { get; set; }
        }
    }
}
